﻿using System;
using System.Collections.Generic;
using System.Text;
using Excel;
namespace ReadFileText
{
    class Class1
    {
        public bool XuatKQTS2Excel(System.Windows.Forms.DataGridView objFGrid, string sFileName)
        {
            try
            {
                int vExcel_rows = objFGrid.Rows.Count;
                if (vExcel_rows > 1)
                {
                    Excel.Application app = new Excel.Application();
                    Excel.Workbook exbook = null;
                    Excel.Worksheet exsheet = null;
                    app.Visible = false;
                    exbook = (Excel.Workbook)(app.Workbooks.Add(true));
                    exsheet = (Excel.Worksheet)exbook.ActiveSheet;

                    Excel.Range rg = exsheet.get_Range("A1", "H1");
                    rg.Merge(true);
                    rg.Select();

                    rg.Font.Bold = true;

                    rg.Font.Name = "Times New Roman";

                    rg.Font.Size = 16;

                    rg.WrapText = true;

                    rg.HorizontalAlignment = Excel.Constants.xlCenter;

                    rg.Interior.ColorIndex = 2;

                    rg.Borders.Weight = 4;

                    rg.Borders.LineStyle = Excel.Constants.xlNone;

                    rg.Cells.RowHeight = 28;
                    rg.Value2 = "DỮ LIỆU DUE HCM";

                    Excel.Range rg2 = exsheet.get_Range("A2", "H2");
                    rg2.Merge(true);
                    rg2.Select();

                    rg2.Font.Bold = true;

                    rg2.Font.Name = "Times New Roman";

                    rg2.Font.Size = 12;

                    rg2.WrapText = true;

                    rg2.HorizontalAlignment = Excel.Constants.xlCenter;

                    rg2.Interior.ColorIndex = 2;

                    rg2.Borders.Weight = 4;

                    rg2.Borders.LineStyle = Excel.Constants.xlNone;

                    rg2.Cells.RowHeight = 28;
                    rg2.Value2 = "Ngày : ";

                    Excel.Range rg3 = exsheet.get_Range("A3", "H3");
                    rg3.Merge(true);
                    rg3.Select();

                    //
                    Excel.Range rg4 = exsheet.get_Range("A4", "H4");

                    rg4.Select();

                    rg4.Font.Bold = true;

                    rg4.Font.Name = "Times New Roman";

                    rg4.Font.Size = 12;

                    rg4.WrapText = true;

                    rg4.HorizontalAlignment = Excel.Constants.xlCenter;

                    rg4.Interior.ColorIndex = 15;

                    rg4.Borders.Weight = 1;

                    rg4.Borders.LineStyle = Excel.Constants.xlNarrow;

                    rg4.Cells.RowHeight = 20;


                    //STT
                    rg4 = exsheet.get_Range("A4", Type.Missing);

                    rg4.Cells.ColumnWidth = 7;

                    rg4.Value2 = "STT";


                    //Chứng khoán
                    rg4 = exsheet.get_Range("B4", Type.Missing);

                    rg4.Cells.ColumnWidth = 15;

                    rg4.Value2 = "Chứng khoán";


                    //Số lượng
                    rg4 = exsheet.get_Range("C4", Type.Missing);

                    rg4.Cells.ColumnWidth = 15;

                    rg4.Value2 = "Số lượng";


                    //Giá
                    rg4 = exsheet.get_Range("D4", Type.Missing);

                    rg4.Cells.ColumnWidth = 15;

                    rg4.Value2 = "Giá";

                    //TK Mua
                    rg4 = exsheet.get_Range("E4", Type.Missing);

                    rg4.Cells.ColumnWidth = 15;

                    rg4.Value2 = "TK Mua";

                    //TK Bán
                    rg4 = exsheet.get_Range("F4", Type.Missing);

                    rg4.Cells.ColumnWidth = 15;

                    rg4.Value2 = "TK Bán";

                    //TK Bán
                    rg4 = exsheet.get_Range("G4", Type.Missing);

                    rg4.Cells.ColumnWidth = 15;

                    rg4.Value2 = "TV Mua";

                    //TK Bán
                    rg4 = exsheet.get_Range("H4", Type.Missing);

                    rg4.Cells.ColumnWidth = 15;

                    rg4.Value2 = "TV Bán";
                    
                    string gSTT = "";
                    //Set data for file exel
                    for (int i = 0; i <= vExcel_rows - 1; i++)
                    {
                        int stt = i + 1;
                        string STT = objFGrid[0,i].Value.ToString();
                        string ChungKhoan = objFGrid[1, i].Value.ToString();
                        string SoLuong = objFGrid[2, i].Value.ToString();
                        string Gia = objFGrid[3, i].Value.ToString();
                        string TkMua = objFGrid[4, i].Value.ToString();
                        string TkBan = objFGrid[5, i].Value.ToString();

                        string TVMua = objFGrid[6, i].Value.ToString();
                        string TVBan = objFGrid[7, i].Value.ToString();

                        if (STT.ToUpper() == gSTT.ToUpper())
                        {
                            STT = "";
                        }
                        else
                        {
                            gSTT = STT;
                        }                        

                        int dong = i + 5;
                        //dinh dang noi dung
                        Excel.Range rgOK = exsheet.get_Range("A" + dong.ToString(), "H" + dong.ToString());

                        rgOK.Select();

                        rgOK.Font.Bold = false;

                        rgOK.Font.Name = "Times New Roman";

                        rgOK.Font.Size = 12;

                        rgOK.WrapText = true;

                        rgOK.HorizontalAlignment = Excel.Constants.xlCenter;

                        rgOK.Interior.ColorIndex = 2;

                        rgOK.Borders.Weight = 1;

                        rgOK.Borders.LineStyle = Excel.Constants.xlNarrow;

                        rgOK.Cells.RowHeight = 15;

                        rgOK = exsheet.get_Range("B" + dong.ToString(), Type.Missing);

                        rgOK.HorizontalAlignment = Excel.Constants.xlCenter;

                        rgOK = exsheet.get_Range("C" + dong.ToString(), Type.Missing);

                        rgOK.HorizontalAlignment = Excel.Constants.xlRight;

                        rgOK = exsheet.get_Range("D" + dong.ToString(), Type.Missing);

                        rgOK.HorizontalAlignment = Excel.Constants.xlRight;

                        exsheet.Cells[dong, 1] = STT;
                        if (ChungKhoan != "")
                            exsheet.Cells[dong, 2] = ChungKhoan;

                        if (SoLuong != "")
                            exsheet.Cells[dong, 3] = SoLuong;

                        if (Gia != "")
                            exsheet.Cells[dong, 4] = Gia;

                        if (TkMua != "")
                            exsheet.Cells[dong, 5] = TkMua;

                        if (TkBan != "")
                            exsheet.Cells[dong, 6] = TkBan;

                        if (TVMua != "")
                            exsheet.Cells[dong, 7] = TVMua;

                        if (TVBan != "")
                            exsheet.Cells[dong, 8] = TVBan;
                    }
                    //footer
                    //dinh dang noi dung
                    int rowFooter = vExcel_rows + 5;
                    Excel.Range rgF = exsheet.get_Range("A" + rowFooter.ToString(), "H" + rowFooter.ToString());

                    rgF.Select();

                    rgF.Font.Bold = true;

                    rgF.Font.Name = "Times New Roman";

                    rgF.Font.Size = 12;

                    rgF.WrapText = true;

                    rgF.HorizontalAlignment = Excel.Constants.xlRight;
                    rgF.Interior.ColorIndex = 15;

                    rgF.Borders.Weight = 1;

                    rgF.Borders.LineStyle = Excel.Constants.xlNarrow;

                    rgF = exsheet.get_Range("H" + rowFooter.ToString(), Type.Missing);

                    rgF.HorizontalAlignment = Excel.Constants.xlCenter;

                    rgF.Cells.RowHeight = 15;
                   
                    exbook.SaveAs(sFileName, Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    app.Quit();
                    app = null;
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch
            {
                return false;
            }
        }
    }
}
